Renewal Journey - Auto Renewal

tip

Approved for development

Summary

The Auto Renewal Journey process at AANP ensures that members are notified and reminded about their upcoming membership renewals, including automatic renewals. This process facilitates seamless membership continuity by sending timely email communications and providing members with necessary reminders and confirmations. The goal is to enhance member retention, minimize lapses in membership, and provide a smooth renewal experience. This system supports automated notifications, payment reminders, and comprehensive communication to ensure that both members and internal staff are well-informed throughout the renewal process.

Requirements

External Users

• As a member with auto-renewal, I should receive an email reminder asking to update payment information 60 days prior to the expiration of membership if the credit card on file will expire before the next renewal date.
• As a member with auto-renewal, I should receive an "Expired Credit Card Email 1" 40 days before the expiration of my membership if my credit card on file is set to expire before the next renewal date.
• As a member with auto-renewal, I should receive an "Expired Credit Card Email 2" 15 days before the expiration of my membership if my credit card on file is set to expire before the next renewal date.
• As a member with auto-renewal, I should receive a renewal reminder email 14 days before the scheduled renewal date.
• As a member with auto-renewal, I should receive a confirmation email after the renewal of membership on the same day as the scheduled renewal date.
• As a member with auto-renewal, I should receive a thank-you email for renewing after the confirmation on the same day as the scheduled renewal date.
• As a member with auto-renewal, I should receive an email if the order fails for the renewal of membership ("Auto Renewal Failure Email 1") 2 days after it fails.
• As a member with auto-renewal, I should receive an email if the order fails for the renewal of membership ("Auto Renewal Failure Email 2") 12 days after it fails.
• As a member with auto-renewal, I should receive a physical letter reminder 15 days after the due date if my auto-renewal fails, reminding me to renew.

Internal Users

• As an internal user, I should be able to schedule and automate the sending of renewal-related emails, ensuring they are sent at the appropriate times (e.g., 60 days, 40 days, 15 days before expiration).
• As an internal user, I should be able to customize and edit email templates for auto-renewal reminders, confirmations, and other communications to ensure the messaging aligns with current campaigns or branding.
• As an internal user, I should be able to monitor the performance of renewal-related emails, including open rates, click-through rates, and bounce rates, to optimize future communications.
• As an internal user, I should be able to update and manage members' communication preferences to ensure that they receive only the emails they have opted in for.
• As an internal user, I should be able to resend any renewal-related emails that a member may have missed or requested to receive again.
• As an internal user, I should be notified of any failed renewal attempts and be able to manually intervene, such as contacting the member directly or triggering an additional follow-up email.
• As an internal user, I should have access to an audit trail of all renewal-related email communications sent to a member, to ensure compliance with organizational policies and regulations.
• As an internal user, I should be able to generate reports on the renewal status of members, including those who have renewed, those whose renewal failed, and those who did not renew, for strategic planning and follow-up actions.
• As an internal user, I should be able to view all renewal-related email communications within the member’s Salesforce record to provide comprehensive support when interacting with members.
• As an internal user, I should be able to collect and analyze feedback from members about the renewal process to continuously improve communication and user experience.

---

Assumptions

• All members will receive the emails outlined if they have active memberships subject to renewal.
• Timelines for email communication will follow the schedule specified in each requirement.
• The renewal reminders and notifications will be integrated with Salesforce and Aptify for seamless tracking and reporting.

---

References

• Aptify - Membership Renewal Process
• Auto-Renewal Communication Journey
• Individual Member Renewing Journey
• Renewal Drop Flow Chart
• Renewal Mailing Procedures
• Physical Mailer Report 1 - Excluding Fellows
• Physical Mailer Report 2- Fellows Only

---

Appendix: Summary of Emails and Physical Mailers in the Auto Renewal Journey

Time Frame	Email/Mailer Type	Description
40 Days Before Scheduled Renewal Date	Expired Credit Card Email 1	Email reminder if the credit card on file will expire before the next renewal date.
15 Days Before Scheduled Renewal Date	Expired Credit Card Email 2	Second email reminder if the credit card on file will expire before the next renewal date.
14 Days Before Scheduled Renewal Date	Renewal Reminder Email	Reminder email sent to all members on auto-renewal that are set to be processed in 14 days.
On Scheduled Renewal Date	Renewal Confirmation Email	Confirmation email sent after successful renewal of membership.
On Scheduled Renewal Date	Thank-You Email	Thank-you email sent after renewal confirmation.
2 Days After Scheduled Renewal Date	Auto Renewal Failure Email 1	Email sent to members if the renewal order fails 2 days after the scheduled renewal date.
12 Days After Scheduled Renewal Date	Auto Renewal Failure Email 2	Second email sent to members if the renewal order fails 12 days after the scheduled renewal date.
15 Days After Scheduled Renewal Date	Physical Letter	Physical letter reminder sent if the auto-renewal fails, reminding the member to renew.
35 days after grace period ends	Exit Survey	Email sent to former members asking for feedback on the renewal process. (Currently 50 days in Aptify, Liz wants 35, set to 35 in SFMC)

Chargent Automated Collection Emails

There is also an Automated Collection email that is currently configured to go out via Salesforce on Stage 3. This overlaps with the emails going out after the auto-renewal failures occur, but includes a magic link for payment requests.

We may want to combine the email from the 12 Days After Scheduled Renewal Date and this Automated Collections email, or turn off the 12 Day email from the journey completely.

Chargent Settings > Automated Collections > Auto-Renewal Retries

Stage	Timing	Attempt Payment	Send Email
Stage 1	1 Day After Initial Attempt	Payment Retry	No
Stage 2	2 Days After Initial Attempt	Payment Retry	No
Stage 3	12 Days After Initial Attempt	Payment Retry	Yes, Automated Collections Payment Request
Stage 4	15 Days After Initial Attempt	No Retry	No, Mark Order as Error

To see the email, go to Email Template - Automated Collections Payment Request.

PAC Auto Renewal Reminder

tip

Change Request

This was not included in the original request. Standalone PAC Contributions have a single Auto-Renewal reminder that goes out 2 Days after a credit card payment failure. This should be configured as a standalone journey, not included in the membership auto-renewal journey.

Bucket Date

Users are pushed into journey buckets to ensure anyone starting in the middle of the journey will be picked up on the next journey action.

Auto-Renewals are a bit more complex than Standard Renewals when it comes to assigning users to a specific Bucket Date for the journey entry points. We want to send out a renewal reminder when the PAYMENT is due, not necessarily being the same as when their Membership is coming due. And, to ensure we are only including memberships that are truly on Auto-Renewal.

In general, you should be looking at the ChargentOrders__ChargentOrder__c.ChargentOrders__Next_Scheduled_Payment__c field, where ChargentOrders__ChargentOrder__c.ChargentOrders__Payment_Status__c is Recurring or Automated Collection. and ChargentOrders__ChargentOrder__c.ChargentOrders__Payment_Frequency__c equal to Annual for all but the PAC Contribution.

Also consider Membership__c.Auto_Renew__c set to TRUE.

Overview

Current Aptify Configuration

These auto renewal renewal emails are combined from Aptify and Real Magnet. They currently all get delivered as separate sends with a separate query for each send.

This makes things easier to control and understand.

Name	Origin	ViewID	MessageTemplateId	Delivery Time
Expired CC 40 days from Auto Renew	Aptify Flow	46120	36	4:45 AM CT
Expired CC 15 days from Auto Renew	Aptify Flow	46119	36	4:45 AM CT
Reminder 14 Days from Auto Renew	Aptify Flow	N/A	N/A	7:30 AM CT
Auto Renew Failure - 2 days	Aptify Flow	46118	37	4:45 AM CT
Auto Renew Failure - 12 days	Aptify Flow	46117	37	6:15 AM CT
50 Days After Expiration (SFMC set to 35 days per Liz)	Real Magnet	RealMagnet_50DaysAfterExpiration	N/A	N/A

!!!! Outlier - PAC

This should be sent out separately, these are the standalone PAC Contributions (with no membership attached)

This is NOT currently included in SFMC but needs to be.

Name	Origin	ViewID	MessageTemplateId	Delivery Time
Standing Order Failure PAC - 2 days	Aptify Flow	47080	46	4:45 AM CT

Process Flows: SendRenewalNotices__c, Send Auto Renewal Reminder Emails__c

Out of Aptify, a process flow runs to deliver a collection of emails each night at 4:45 AM CT

Analysis

EXEC SpGetActiveMembershipEmailSchedules
-- To pull the actual SQL being used:
SELECT * 
FROM Aptify.dbo.ViewSql 
WHERE ViewId IN (
	46120,46119,46118,47080,46117,46118,46117,47080
)
--To pull the actual message template being used:
SELECT ID, [Name], [Description], [Category], [Subject], TextBody, HTMLBody, FromName, FromEmail, ReplyTo 
FROM Aptify.dbo.vwMessageTemplates WHERE ID IN (
	36,37,46
)

40 Days Prior

40 Days Before Expiration

   SELECT
       APTIFY..vwSubscriptionsStandingOrder.ID,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberID,
       APTIFY..vwSubscriptionsStandingOrder.Subscriber,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberEmail__c,
       APTIFY..vwSubscriptionsStandingOrder.Frequency,
       APTIFY..vwSubscriptionsStandingOrder.PaymentType,
       APTIFY..vwSubscriptionsStandingOrder.CCExpireDate,
       APTIFY..vwSubscriptionsStandingOrder.CCPartial,
       APTIFY..vwSubscriptionsStandingOrder.ScheduledDate,
       APTIFY..vwSubscriptionsStandingOrder.IsComplete,
       APTIFY..vwSubscriptionsStandingOrder.CALC_EndDate_NoTime
   FROM 
       APTIFY..vwSubscriptionsStandingOrder 

   WHERE    

       APTIFY..vwSubscriptionsStandingOrder.Status = N'Active'
   AND        APTIFY..vwSubscriptionsStandingOrder.CCExpireDate <= CONVERT(char(8),(DateAdd(day, 40,GETDATE())), 112)
   AND        APTIFY..vwSubscriptionsStandingOrder.Frequency = N'Annually'
   AND        APTIFY..vwSubscriptionsStandingOrder.ScheduledDate = CONVERT(char(8),(DateAdd(day, 40,GETDATE())), 112)

15 Days Prior

15 Days Before Expiration

   SELECT
       APTIFY..vwSubscriptionsStandingOrder.ID,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberID,
       APTIFY..vwSubscriptionsStandingOrder.Subscriber,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberEmail__c,
       APTIFY..vwSubscriptionsStandingOrder.Frequency,
       APTIFY..vwSubscriptionsStandingOrder.PaymentType,
       APTIFY..vwSubscriptionsStandingOrder.CCExpireDate,
       APTIFY..vwSubscriptionsStandingOrder.CCPartial,
       APTIFY..vwSubscriptionsStandingOrder.ScheduledDate,
       APTIFY..vwSubscriptionsStandingOrder.IsComplete,
       APTIFY..vwSubscriptionsStandingOrder.CALC_EndDate_NoTime
   FROM 
       APTIFY..vwSubscriptionsStandingOrder 

   WHERE    

       APTIFY..vwSubscriptionsStandingOrder.Status = N'Active'
   AND        APTIFY..vwSubscriptionsStandingOrder.CCExpireDate <= CONVERT(char(8),(DateAdd(day, 15,GETDATE())), 112)
   AND        APTIFY..vwSubscriptionsStandingOrder.Frequency = N'Annually'
   AND        APTIFY..vwSubscriptionsStandingOrder.ScheduledDate = CONVERT(char(8),(DateAdd(day, 15,GETDATE())), 112)

14 Days Prior

14 Days Before Expiration

Sends a reminder email with product and cost detail for review

	SELECT DISTINCT so.ID, so.ShipToID, so.ShipToFirstName, so.ShipToEmail, BasePaymentType, DateExpires,  sos.ScheduledDate, sos.IsComplete, so.[Status], so.CCExpireDate, so.PaymentInformationID
	INTO #tbl_orders
	FROM vwStandingOrders so 
	INNER JOIN Aptify.dbo.vwStandingOrderSchedule sos ON sos.StandingOrderID = so.ID
	INNER JOIN Aptify.dbo.PaymentType pt WITH (NOLOCK) ON pt.ID = so.PaymentTypeID
	WHERE (
	so.BasePaymentType='Credit Card Reference Transaction' 
	OR so.BasePaymentType='Credit Card'
	)
	AND (
	so.DateExpires >=GETDATE()
	OR so.DateExpires=''
	) 
	--AND so.CCExpireDate > CAST(getdate() AS DATE)
	--If the PaymentType is related to BluePay, then let Expired Cards through.  CardUpdater might fix it.
	AND 1 = CASE WHEN pt.[Description] LIKE '%BluePay%' THEN 1 ELSE CASE WHEN so.CCExpireDate > CAST(getdate() AS DATE) THEN 1 ELSE 0 END END
	AND so.Status='Active'
	AND sos.ScheduledDate <= getdate()
	AND sos.IsComplete <> 1
	--AND so.ReferenceTransactionNumber LIKE '1%'
	ORDER BY ScheduledDate

	-- FIND THOSE OLDER ITEMS THAT HAVE HAD THEIR STANDING ORDERS PAYMENT INFORMATION UPDATED
	SELECT o.ID, MAX(p.DateUpdated) AS DateUpdated
	INTO #tbl_updated
	FROM #tbl_orders o 
	INNER JOIN Aptify.dbo.PaymentInformation p WITH (NOLOCK) ON p.ID = o.PaymentInformationID
	WHERE DateUpdated BETWEEN CAST(DATEADD(DAY, -2, CAST(getdate() AS DATE)) AS DATE) AND getdate()
	--AND p.ReferenceTransactionNumber LIKE '1%'
	GROUP BY o.ID

	TRUNCATE TABLE AANPDB.dbo.StandingOrders_NightlyQueue;

	INSERT INTO AANPDB.dbo.StandingOrders_NightlyQueue (ID, DateCreated)
	SELECT DISTINCT o.ID, getdate() AS DateCreated
	FROM #tbl_orders o
	LEFT OUTER JOIN #tbl_updated u ON u.ID = o.ID
	WHERE u.ID IS NOT NULL OR (
		o.ScheduledDate BETWEEN CAST(DATEADD(DAY, -2, getdate()) AS DATE) AND CAST(getdate() AS DATE)
	)
	ORDER BY ID

2 Days After

2 Days After Expiration

   SELECT
       APTIFY..vwSubscriptionsStandingOrder.ID,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberID,
       APTIFY..vwSubscriptionsStandingOrder.Subscriber,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberEmail__c,
       APTIFY..vwSubscriptionsStandingOrder.Frequency,
       APTIFY..vwSubscriptionsStandingOrder.PaymentType,
       APTIFY..vwSubscriptionsStandingOrder.CCExpireDate,
       APTIFY..vwSubscriptionsStandingOrder.CCPartial,
       APTIFY..vwSubscriptionsStandingOrder.ScheduledDate,
       APTIFY..vwSubscriptionsStandingOrder.IsComplete,
       APTIFY..vwSubscriptionsStandingOrder.CALC_EndDate_NoTime
   FROM 
       APTIFY..vwSubscriptionsStandingOrder 

   WHERE    

       APTIFY..vwSubscriptionsStandingOrder.Status = N'Active'
   AND        APTIFY..vwSubscriptionsStandingOrder.IsComplete = 0
   AND        APTIFY..vwSubscriptionsStandingOrder.Frequency = N'Annually'
   AND        APTIFY..vwSubscriptionsStandingOrder.ScheduledDate = CONVERT(char(8),(DateAdd(day,-2,GETDATE())), 112)

12 Days After

12 Days After Expiration

   SELECT
       APTIFY..vwSubscriptionsStandingOrder.ID,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberID,
       APTIFY..vwSubscriptionsStandingOrder.Subscriber,
       APTIFY..vwSubscriptionsStandingOrder.SubscriberEmail__c,
       APTIFY..vwSubscriptionsStandingOrder.Frequency,
       APTIFY..vwSubscriptionsStandingOrder.PaymentType,
       APTIFY..vwSubscriptionsStandingOrder.CCExpireDate,
       APTIFY..vwSubscriptionsStandingOrder.CCPartial,
       APTIFY..vwSubscriptionsStandingOrder.ScheduledDate,
       APTIFY..vwSubscriptionsStandingOrder.IsComplete,
       APTIFY..vwSubscriptionsStandingOrder.CALC_EndDate_NoTime
   FROM 
       APTIFY..vwSubscriptionsStandingOrder 

   WHERE    

       APTIFY..vwSubscriptionsStandingOrder.Status = N'Active'
   AND        APTIFY..vwSubscriptionsStandingOrder.IsComplete = 0
   AND        APTIFY..vwSubscriptionsStandingOrder.Frequency = N'Annually'
   AND        APTIFY..vwSubscriptionsStandingOrder.ScheduledDate = CONVERT(char(8),(DateAdd(day,-12,GETDATE())), 112)

35 Days After

35 Days After Expiration

This is a Real Magnet View: RealMagnet_35DaysAfterExpiration

In Aptify, this is currently going out at 50 days after expiration. But talking with Liz, she wants this to be after 35 days, which is what is already setup in SFMC today.

   SELECT
       APTIFY..vwSubscriptions.ID 'SubscriptionID',
       APTIFY..vwSubscriptions.SubscriberID 'ID',
       APTIFY..vwSubscriptions.Subscriber 'SubscriberName',
       LEFT(APTIFY..vwSubscriptions.Subscriber, charindex(' ', APTIFY..vwSubscriptions.Subscriber) - 1) AS FirstName,
       APTIFY..vwSubscriptions.SubscriberEmail__c,
       APTIFY..vwSubscriptions.Product,
       APTIFY..vwSubscriptions.Purchases,
       APTIFY..vwSubscriptions.StartDate,
       APTIFY..vwSubscriptions.EndDate,
       APTIFY..vwSubscriptions.CALC_EndDate_NoTime
   FROM 
   APTIFY..vwSubscriptions 
   INNER JOIN APTIFY.dbo.vwPersons  ON APTIFY.dbo.vwPersons.ID = APTIFY..vwSubscriptions.RecipientID
   WHERE APTIFY..vwSubscriptions.SubscriberEmail__c LIKE '%@%.%'
       
   AND APTIFY..vwSubscriptions.AutoRenew = 0
       AND APTIFY..vwSubscriptions.ProductID_IsDues = 1
       AND APTIFY..vwSubscriptions.[Status] = 'Active'
       AND APTIFY.dbo.vwPersons.[Status] = 1
       AND APTIFY.dbo.vwPersons.RenewalEmailsExclude__AANP = 0
       AND ((APTIFY.dbo.vwPersons.DuesPaidThru_DateOnly = CAST(DATEADD(DAY, -35, GETDATE()) AS DATE) 
       AND CAST(APTIFY..vwSubscriptions.EndDate AS DATE) = CAST(DATEADD(DAY, -35, GETDATE()) AS DATE))
       AND APTIFY..vwSubscriptions.Subscriber
   ID NOT IN (
               SELECT PersonID
               FROM APTIFY.dbo.vwPersonFunctions
               WHERE FunctionID_Name = 'Employee'
       )
       AND APTIFY..vwPersons.EmailExclude = 0
       AND APTIFY..vwPersons.ID NOT IN 
       (
           SELECT PersonID
           FROM AANPDB..vwEmailPreferences
           WHERE Campaigner_EmailCategoryId = 8
       ))

PAC 2 Days After

PAC 2 Days After Expiration

Warning! This does not exist today in SFMC. I would add this as a separate one email journey rather than try to include this in the Auto-Renewal journey. This is for STANDALONE PAC CONTRIBUTIONS.

Separate send for Auto-Renewals for standalone PAC Contributions, separate from membership auto renewals.

   SELECT DISTINCT
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ID,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.Frequency,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ShipTo,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ScheduledDate,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.IsComplete,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ShipToID,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ShipToEmail,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.SubscriberID,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.SubscriberEmail__c,
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.Status
   FROM 
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp 
   WHERE    
       APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.Status = N'Active'
   AND        APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.Frequency  NOT LIKE  N'%Annually%'
   AND        APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.IsComplete = 0
   AND        APTIFY..vwStandingOrdersWithSubscriberInfo__aanp.ScheduledDate = CONVERT(char(8),(DateAdd(day,-2,GETDATE())), 112)

Approvals / Signoffs

Role	Name	Signature	Date
Technical Lead	Jeff Sikes	JS	07/26/2024
Stakeholder	Zeke Holliman	ZH	08/16/2024

Revisions

Date	Author	Changes
10/14/2025	Jeff Sikes	Updated renewal journey path info, added automated collections.